<?php
/**
 * User : Ciddy Natadisastra
 * Create Date: 7/26/11
 * Change Date: 7/26/11
 * File Description:
 *
 *
 *
 */
 
class db extends models{
    const WHERE_FRAGMENT = 0;
    const WHERE_VALUES = 1;

    protected static $_config = array(
        'connection_string' => 'sqlite::memory:',
        'id_column' => 'id',
        'id_column_overrides' => array(),
        'error_mode' => PDO::ERRMODE_EXCEPTION,
        'username' => null,
        'password' => null,
        'driver_options' => null,
        'identifier_quote_character' => null,
        'logging' => false,
        'caching' => false,
    );

    protected static $_db;
    protected static $_last_query;
    protected static $_query_log = array();
    protected static $_query_cache = array();
    protected $_table_name;
    protected $_table_alias = null;
    protected $_values = array();
    protected $_result_columns = array('*');
    protected $_using_default_result_columns = true;
    protected $_join_sources = array();
    protected $_distinct = false;
    protected $_is_raw_query = false;
    protected $_raw_query = '';
    protected $_raw_parameters = array();
    protected $_where_conditions = array();
    protected $_limit = null;
    protected $_offset = null;
    protected $_order_by = array();
    protected $_group_by = array();
    protected $_data = array();
    protected $_dirty_fields = array();
    protected $_is_new = false;
    protected $_instance_id_column = null;

    public static function configure($key, $value=null) {
        if (is_null($value)) {
            $value = $key;
            $key = 'connection_string';
        }
        self::$_config[$key] = $value;
    }

    public static function for_table($table_name) {
        self::_setup_db();
        return new self($table_name);
    }

    protected static function _setup_db() {
        if (!is_object(self::$_db)) {
            $connection_string = self::$_config['connection_string'];
            $username = self::$_config['username'];
            $password = self::$_config['password'];
            $driver_options = self::$_config['driver_options'];
            $db = new PDO($connection_string, $username, $password, $driver_options);
            $db->setAttribute(PDO::ATTR_ERRMODE, self::$_config['error_mode']);
            self::set_db($db);
        }
    }

    public static function set_db($db) {
        self::$_db = $db;
        self::_setup_identifier_quote_character();
    }

    public static function _setup_identifier_quote_character() {
        if (is_null(self::$_config['identifier_quote_character'])) {
            self::$_config['identifier_quote_character'] = self::_detect_identifier_quote_character();
        }
    }

    protected static function _detect_identifier_quote_character() {
        switch(self::$_db->getAttribute(PDO::ATTR_DRIVER_NAME)) {
            case 'pgsql':
            case 'sqlsrv':
            case 'dblib':
            case 'mssql':
            case 'sybase':
                return '"';
            case 'mysql':
            case 'sqlite':
            case 'sqlite2':
            default:
                return '`';
        }
    }

    public static function get_db() {
        self::_setup_db();
        return self::$_db;
    }

    protected static function _log_query($query, $parameters) {
        if (!self::$_config['logging']) {
            return false;
        }

        if (count($parameters) > 0) {
            $parameters = array_map(array(self::$_db, 'quote'), $parameters);
            $query = str_replace("?", "%s", $query);
            $bound_query = vsprintf($query, $parameters);
        } else {
            $bound_query = $query;
        }

        self::$_last_query = $bound_query;
        self::$_query_log[] = $bound_query;
        return true;
    }

    public static function get_last_query() {
            return self::$_last_query;
    }

    public static function get_query_log() {
        return self::$_query_log;
    }

    protected function __construct($table_name, $data=array()) {
        $this->_table_name = $table_name;
        $this->_data = $data;
    }

    public function create($data=null) {
        $this->_is_new = true;
        if (!is_null($data)) {
            return $this->hydrate($data)->force_all_dirty();
        }
        return $this;
    }

    public function use_id_column($id_column) {
        $this->_instance_id_column = $id_column;
        return $this;
    }

    protected function _create_instance_from_row($row) {
        $instance = self::for_table($this->_table_name);
        $instance->use_id_column($this->_instance_id_column);
        $instance->hydrate($row);
        return $instance;
    }

    public function find_one($id=null) {
        if (!is_null($id)) {
            $this->where_id_is($id);
        }
        $this->limit(1);
        $rows = $this->_run();

        if (empty($rows)) {
            return false;
        }

        return $this->_create_instance_from_row($rows[0]);
    }


    public function find_many() {
        $rows = $this->_run();
        return array_map(array($this, '_create_instance_from_row'), $rows);
    }

    public function count() {
        $this->select_expr('COUNT(*)', 'count');
        $result = $this->find_one();
        return ($result !== false && isset($result->count)) ? (int) $result->count : 0;
    }

    public function hydrate($data=array()) {
        $this->_data = $data;
        return $this;
    }

    public function force_all_dirty() {
        $this->_dirty_fields = $this->_data;
        return $this;
    }

    public function raw_query($query, $parameters) {
        $this->_is_raw_query = true;
        $this->_raw_query = $query;
        $this->_raw_parameters = $parameters;
        return $this;
    }

    public function table_alias($alias) {
        $this->_table_alias = $alias;
        return $this;
    }

    protected function _add_result_column($expr, $alias=null) {
        if (!is_null($alias)) {
            $expr .= " AS " . $this->_quote_identifier($alias);
        }

        if ($this->_using_default_result_columns) {
            $this->_result_columns = array($expr);
            $this->_using_default_result_columns = false;
        } else {
            $this->_result_columns[] = $expr;
        }
        return $this;
    }

    public function select($column, $alias=null) {
        $column = $this->_quote_identifier($column);
        return $this->_add_result_column($column, $alias);
    }

    public function select_expr($expr, $alias=null) {
        return $this->_add_result_column($expr, $alias);
    }

    public function distinct() {
        $this->_distinct = true;
        return $this;
    }

    protected function _add_join_source($join_operator, $table, $constraint, $table_alias=null) {

        $join_operator = trim("{$join_operator} JOIN");

        $table = $this->_quote_identifier($table);

        if (!is_null($table_alias)) {
            $table_alias = $this->_quote_identifier($table_alias);
            $table .= " {$table_alias}";
        }

        if (is_array($constraint)) {
            list($first_column, $operator, $second_column) = $constraint;
            $first_column = $this->_quote_identifier($first_column);
            $second_column = $this->_quote_identifier($second_column);
            $constraint = "{$first_column} {$operator} {$second_column}";
        }

        $this->_join_sources[] = "{$join_operator} {$table} ON {$constraint}";
        return $this;
    }

    public function join($table, $constraint, $table_alias=null) {
        return $this->_add_join_source("", $table, $constraint, $table_alias);
    }

    public function inner_join($table, $constraint, $table_alias=null) {
        return $this->_add_join_source("INNER", $table, $constraint, $table_alias);
    }

    public function left_outer_join($table, $constraint, $table_alias=null) {
        return $this->_add_join_source("LEFT OUTER", $table, $constraint, $table_alias);
    }

    public function right_outer_join($table, $constraint, $table_alias=null) {
        return $this->_add_join_source("RIGHT OUTER", $table, $constraint, $table_alias);
    }

    public function full_outer_join($table, $constraint, $table_alias=null) {
        return $this->_add_join_source("FULL OUTER", $table, $constraint, $table_alias);
    }

    protected function _add_where($fragment, $values=array()) {
        if (!is_array($values)) {
            $values = array($values);
        }
        $this->_where_conditions[] = array(
            self::WHERE_FRAGMENT => $fragment,
            self::WHERE_VALUES => $values,
        );
        return $this;
    }

    protected function _add_simple_where($column_name, $separator, $value) {
        $column_name = $this->_quote_identifier($column_name);
        return $this->_add_where("{$column_name} {$separator} ?", $value);
    }

    protected function _create_placeholders($number_of_placeholders) {
        return join(", ", array_fill(0, $number_of_placeholders, "?"));
    }

    public function where($column_name, $value) {
        return $this->where_equal($column_name, $value);
    }

    public function where_equal($column_name, $value) {
        return $this->_add_simple_where($column_name, '=', $value);
    }

    public function where_not_equal($column_name, $value) {
        return $this->_add_simple_where($column_name, '!=', $value);
    }

    public function where_id_is($id) {
        return $this->where($this->_get_id_column_name(), $id);
    }

    public function where_like($column_name, $value) {
        return $this->_add_simple_where($column_name, 'LIKE', $value);
    }

    public function where_not_like($column_name, $value) {
        return $this->_add_simple_where($column_name, 'NOT LIKE', $value);
    }

    public function where_gt($column_name, $value) {
        return $this->_add_simple_where($column_name, '>', $value);
    }

    public function where_lt($column_name, $value) {
        return $this->_add_simple_where($column_name, '<', $value);
    }

    public function where_gte($column_name, $value) {
        return $this->_add_simple_where($column_name, '>=', $value);
    }

    public function where_lte($column_name, $value) {
        return $this->_add_simple_where($column_name, '<=', $value);
    }

    public function where_in($column_name, $values) {
        $column_name = $this->_quote_identifier($column_name);
        $placeholders = $this->_create_placeholders(count($values));
        return $this->_add_where("{$column_name} IN ({$placeholders})", $values);
    }

    public function where_not_in($column_name, $values) {
        $column_name = $this->_quote_identifier($column_name);
        $placeholders = $this->_create_placeholders(count($values));
        return $this->_add_where("{$column_name} NOT IN ({$placeholders})", $values);
    }

    public function where_null($column_name) {
        $column_name = $this->_quote_identifier($column_name);
        return $this->_add_where("{$column_name} IS NULL");
    }

    public function where_not_null($column_name) {
        $column_name = $this->_quote_identifier($column_name);
        return $this->_add_where("{$column_name} IS NOT NULL");
    }

    public function where_raw($clause, $parameters=array()) {
        return $this->_add_where($clause, $parameters);
    }

    public function limit($limit) {
        $this->_limit = $limit;
        return $this;
    }

    public function offset($offset) {
        $this->_offset = $offset;
        return $this;
    }

    protected function _add_order_by($column_name, $ordering) {
        $column_name = $this->_quote_identifier($column_name);
        $this->_order_by[] = "{$column_name} {$ordering}";
        return $this;
    }

    public function order_by_desc($column_name) {
        return $this->_add_order_by($column_name, 'DESC');
    }

    public function order_by_asc($column_name) {
        return $this->_add_order_by($column_name, 'ASC');
    }

    public function group_by($column_name) {
        $column_name = $this->_quote_identifier($column_name);
        $this->_group_by[] = $column_name;
        return $this;
    }

    protected function _build_select() {
        if ($this->_is_raw_query) {
            $this->_values = $this->_raw_parameters;
            return $this->_raw_query;
        }

        return $this->_join_if_not_empty(" ", array(
            $this->_build_select_start(),
            $this->_build_join(),
            $this->_build_where(),
            $this->_build_group_by(),
            $this->_build_order_by(),
            $this->_build_limit(),
            $this->_build_offset(),
        ));
    }

    protected function _build_select_start() {
        $result_columns = join(', ', $this->_result_columns);

        if ($this->_distinct) {
            $result_columns = 'DISTINCT ' . $result_columns;
        }

        $fragment = "SELECT {$result_columns} FROM " . $this->_quote_identifier($this->_table_name);

        if (!is_null($this->_table_alias)) {
            $fragment .= " " . $this->_quote_identifier($this->_table_alias);
        }
        return $fragment;
    }

    protected function _build_join() {
        if (count($this->_join_sources) === 0) {
            return '';
        }

        return join(" ", $this->_join_sources);
    }

    protected function _build_where() {
        if (count($this->_where_conditions) === 0) {
            return '';
        }

        $where_conditions = array();
        foreach ($this->_where_conditions as $condition) {
            $where_conditions[] = $condition[self::WHERE_FRAGMENT];
            $this->_values = array_merge($this->_values, $condition[self::WHERE_VALUES]);
        }

        return "WHERE " . join(" AND ", $where_conditions);
    }

    protected function _build_group_by() {
        if (count($this->_group_by) === 0) {
            return '';
        }
        return "GROUP BY " . join(", ", $this->_group_by);
    }

    protected function _build_order_by() {
        if (count($this->_order_by) === 0) {
            return '';
        }
        return "ORDER BY " . join(", ", $this->_order_by);
    }

    protected function _build_limit() {
        if (!is_null($this->_limit)) {
            return "LIMIT " . $this->_limit;
        }
        return '';
    }

    protected function _build_offset() {
        if (!is_null($this->_offset)) {
            return "OFFSET " . $this->_offset;
        }
        return '';
    }

    protected function _join_if_not_empty($glue, $pieces) {
        $filtered_pieces = array();
        foreach ($pieces as $piece) {
            if (is_string($piece)) {
                $piece = trim($piece);
            }
            if (!empty($piece)) {
                $filtered_pieces[] = $piece;
            }
        }
        return join($glue, $filtered_pieces);
    }

    protected function _quote_identifier($identifier) {
        $parts = explode('.', $identifier);
        $parts = array_map(array($this, '_quote_identifier_part'), $parts);
        return join('.', $parts);
    }

    protected function _quote_identifier_part($part) {
        if ($part === '*') {
            return $part;
        }
        $quote_character = self::$_config['identifier_quote_character'];
        return $quote_character . $part . $quote_character;
    }

    protected static function _create_cache_key($query, $parameters) {
        $parameter_string = join(',', $parameters);
        $key = $query . ':' . $parameter_string;
        return sha1($key);
    }

    protected static function _check_query_cache($cache_key) {
        if (isset(self::$_query_cache[$cache_key])) {
            return self::$_query_cache[$cache_key];
        }
        return false;
    }

    public static function clear_cache() {
        self::$_query_cache = array();
    }

    protected static function _cache_query_result($cache_key, $value) {
        self::$_query_cache[$cache_key] = $value;
    }

    protected function _run() {
        $query = $this->_build_select();
        $caching_enabled = self::$_config['caching'];

        if ($caching_enabled) {
            $cache_key = self::_create_cache_key($query, $this->_values);
            $cached_result = self::_check_query_cache($cache_key);

            if ($cached_result !== false) {
                return $cached_result;
            }
        }

        self::_log_query($query, $this->_values);
        $statement = self::$_db->prepare($query);
        $statement->execute($this->_values);

        $rows = array();
        while ($row = $statement->fetch(PDO::FETCH_ASSOC)) {
            $rows[] = $row;
        }

        if ($caching_enabled) {
            self::_cache_query_result($cache_key, $rows);
        }

        return $rows;
    }

    public function as_array() {
        if (func_num_args() === 0) {
            return $this->_data;
        }
        $args = func_get_args();
        return array_intersect_key($this->_data, array_flip($args));
    }

    public function get($key) {
        return isset($this->_data[$key]) ? $this->_data[$key] : null;
    }

    protected function _get_id_column_name() {
        if (!is_null($this->_instance_id_column)) {
            return $this->_instance_id_column;
        }
        if (isset(self::$_config['id_column_overrides'][$this->_table_name])) {
            return self::$_config['id_column_overrides'][$this->_table_name];
        } else {
            return self::$_config['id_column'];
        }
    }

    public function id() {
        return $this->get($this->_get_id_column_name());
    }

    public function set($key, $value) {
        $this->_data[$key] = $value;
        $this->_dirty_fields[$key] = $value;
    }

    public function is_dirty($key) {
        return isset($this->_dirty_fields[$key]);
    }

    public function save() {
        $query = array();
        $values = array_values($this->_dirty_fields);

        if (!$this->_is_new) {
            if (count($values) == 0) {
                return true;
            }
            $query = $this->_build_update();
            $values[] = $this->id();
        } else {
            $query = $this->_build_insert();
        }

        self::_log_query($query, $values);
        $statement = self::$_db->prepare($query);
        $success = $statement->execute($values);

        if ($this->_is_new) {
            $this->_is_new = false;
            if (is_null($this->id())) {
                $this->_data[$this->_get_id_column_name()] = self::$_db->lastInsertId();
            }
        }

        $this->_dirty_fields = array();
        return $success;
    }

    protected function _build_update() {
        $query = array();
        $query[] = "UPDATE {$this->_quote_identifier($this->_table_name)} SET";

        $field_list = array();
        foreach ($this->_dirty_fields as $key => $value) {
            $field_list[] = "{$this->_quote_identifier($key)} = ?";
        }
        $query[] = join(", ", $field_list);
        $query[] = "WHERE";
        $query[] = $this->_quote_identifier($this->_get_id_column_name());
        $query[] = "= ?";
        return join(" ", $query);
    }

    protected function _build_insert() {
        $query[] = "INSERT INTO";
        $query[] = $this->_quote_identifier($this->_table_name);
        $field_list = array_map(array($this, '_quote_identifier'), array_keys($this->_dirty_fields));
        $query[] = "(" . join(", ", $field_list) . ")";
        $query[] = "VALUES";

        $placeholders = $this->_create_placeholders(count($this->_dirty_fields));
        $query[] = "({$placeholders})";
        return join(" ", $query);
    }

    public function delete() {
        $query = join(" ", array(
            "DELETE FROM",
            $this->_quote_identifier($this->_table_name),
            "WHERE",
            $this->_quote_identifier($this->_get_id_column_name()),
            "= ?",
        ));
        $params = array($this->id());
        self::_log_query($query, $params);
        $statement = self::$_db->prepare($query);
        return $statement->execute($params);
    }

    public function __get($key) {
        return $this->get($key);
    }

    public function __set($key, $value) {
        $this->set($key, $value);
    }

    public function __isset($key) {
        return isset($this->_data[$key]);
    }

}
